﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization;

namespace DiveManager
{
    public class SyncDatabase
    {
        public int transactionCount;
        public uint BatchSize = 50000;
        public uint MemorySize = 100000;

        private List<string> tabellennamen = new List<string>();

        const string localDatabase = "DiveDatabase";
        const string remoteDatabase = "DiveDatabaseRemote";
        
        public SyncDatabase()
        {
            tabellennamen.Add("DiveActivities");
            tabellennamen.Add("DiveCustomers");
            tabellennamen.Add("DiveGuides");
            tabellennamen.Add("RelationCustomersActivities");
            tabellennamen.Add("RelationGuidesActivities");
            tabellennamen.Add("User");
            tabellennamen.Add("RelationUserSecuritygroupRight");
            tabellennamen.Add("SecurityGroups");
            tabellennamen.Add("SecurityRights");
            tabellennamen.Add("RelationSecuritygroupsSecurityrights");
            tabellennamen.Add("Customer");
            tabellennamen.Add("ValueAccounts");
            tabellennamen.Add("ProductHeadGroups");
            tabellennamen.Add("ProductSubGroup");

        }

        public string StartSync()
        {
            // SQL Connections
            SqlConnection localSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalDatabase"].ConnectionString);
            SqlConnection remoteSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["RemoteDatabase"].ConnectionString);
            string Statistics = System.Environment.NewLine;

            foreach (string tabName in tabellennamen)
            {
                string tmp_SyncProvider = localDatabase + ".dbo." + tabName;
                SqlSyncProvider LocalProvider = GetSqlSyncProvider(tmp_SyncProvider, localSqlConnection);
                tmp_SyncProvider = remoteDatabase + ".dbo." + tabName;
                SqlSyncProvider RemoteProvider = GetSqlSyncProvider(tmp_SyncProvider, remoteSqlConnection);
                SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
                syncOrchestrator.LocalProvider = LocalProvider;
                syncOrchestrator.RemoteProvider = RemoteProvider;
                syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;
                Statistics += System.Environment.NewLine + tabName + ":";
                Statistics += System.Environment.NewLine + ShowStatistics(syncOrchestrator.Synchronize());                
            }
            //Close Connections
            localSqlConnection.Close();
            remoteSqlConnection.Close();

            return Statistics;
        }

        public void Setup()
        {
            // SQL Connections
            SqlConnection localSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalDatabase"].ConnectionString);
            SqlConnection remoteSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["RemoteDatabase"].ConnectionString);

            foreach (string tabName in tabellennamen)
            {
                string tmp_string = "dbo." + tabName;
                DbSyncTableDescription tabledesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tmp_string, localSqlConnection);
                tmp_string = localDatabase + ".dbo." + tabName;
                DbSyncScopeDescription ScopeLocal = new DbSyncScopeDescription(tmp_string);
                tmp_string = remoteDatabase + ".dbo." + tabName;
                DbSyncScopeDescription ScopeRemote = new DbSyncScopeDescription(tmp_string);
                ScopeLocal.Tables.Add(tabledesc);
                ScopeRemote.Tables.Add(tabledesc);

                SqlSyncScopeProvisioning localSqlServerProv = new SqlSyncScopeProvisioning(localSqlConnection, ScopeLocal);
                tmp_string = localDatabase + ".dbo." + tabName;
                if (!localSqlServerProv.ScopeExists(tmp_string)) localSqlServerProv.Apply();


                SqlSyncScopeProvisioning remoteSqlServerProv = new SqlSyncScopeProvisioning(remoteSqlConnection, ScopeRemote);
                tmp_string = remoteDatabase + ".dbo." + tabName;
                if (!remoteSqlServerProv.ScopeExists(tmp_string)) remoteSqlServerProv.Apply();
            }
            // close Connections
            localSqlConnection.Close();
            remoteSqlConnection.Close();
        }

        public void Remove()
        {
            // SQL Connections
            SqlConnection localSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalDatabase"].ConnectionString);
            SqlConnection remoteSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["RemoteDatabase"].ConnectionString);

            foreach (string tabName in tabellennamen)
            {
                string tmp_string = localDatabase + "dbo." + tabName;
                DbSyncScopeDescription Scope = new DbSyncScopeDescription(tmp_string);
                tmp_string = "dbo." + tabName;
                DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tmp_string, localSqlConnection);
                SqlSyncScopeDeprovisioning localSqlServerDeprov = new SqlSyncScopeDeprovisioning(localSqlConnection);
                tmp_string = localDatabase + ".dbo." + tabName;
                localSqlServerDeprov.DeprovisionScope(tmp_string);
                SqlSyncScopeDeprovisioning remoteSqlServerDeprov = new SqlSyncScopeDeprovisioning(remoteSqlConnection);
                tmp_string = remoteDatabase + ".dbo." + tabName;
                remoteSqlServerDeprov.DeprovisionScope(tmp_string);
            }
            // close Connections
            localSqlConnection.Close();
            remoteSqlConnection.Close();
        }

        private SqlSyncProvider GetSqlSyncProvider(string scope, SqlConnection con)
        {
            SqlSyncProvider sqlSyncProvider = new SqlSyncProvider(scope, con);

            //Set TimeOut
            sqlSyncProvider.CommandTimeout = 30 * 60;

            //Set memory allocation to the database providers
            sqlSyncProvider.MemoryDataCacheSize = MemorySize;

            //Set application transaction size on destination provider.
            sqlSyncProvider.ApplicationTransactionSize = BatchSize;

            return sqlSyncProvider;
        }

        private string ShowStatistics(SyncOperationStatistics syncStats)
        {
            string message;
            message = "\tSync Start Time :" + syncStats.SyncStartTime.ToString() + System.Environment.NewLine;
            message += "\tSync End Time   :" + syncStats.SyncEndTime.ToString() + System.Environment.NewLine;
            message += "\tUpload Changes Applied :" + syncStats.UploadChangesApplied.ToString() + System.Environment.NewLine;
            message += "\tUpload Changes Failed  :" + syncStats.UploadChangesFailed.ToString() + System.Environment.NewLine;
            message += "\tUpload Changes Total   :" + syncStats.UploadChangesTotal.ToString() + System.Environment.NewLine;
            message += "\tDownload Changes Applied :" + syncStats.DownloadChangesApplied.ToString() + System.Environment.NewLine;
            message += "\tDownload Changes Failed  :" + syncStats.DownloadChangesFailed.ToString() + System.Environment.NewLine;
            message += "\tDownload Changes Total   :" + syncStats.DownloadChangesTotal.ToString() + System.Environment.NewLine;
            return message;
        }
    }
}
